﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
namespace cuyan.orm
{
    public class CuyanClient : SqlRun
    {
        public override string ConnectionString { get; set; }
        #region Tool
        public static IList<T> ConvertTo<T>(DataTable table)
        {
            if (table == null)
            {
                return null;
            }
            List<DataRow> rows = new List<DataRow>();
            foreach (DataRow row in table.Rows)
            {
                rows.Add(row);
            }
            return ConvertTo<T>(rows);
        }
        public static IList<T> ConvertTo<T>(IList<DataRow> rows)
        {
            IList<T> list = null;
            if (rows != null)
            {
                list = new List<T>();
                foreach (DataRow row in rows)
                {
                    T item = CreateItem<T>(row);
                    list.Add(item);
                }
            }
            return list;
        }
        public static T CreateItem<T>(DataRow row)
        {
            T obj = default(T);
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();
                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
                    try
                    {
                        object value = row[column.ColumnName];
                        prop.SetValue(obj, value, null);
                    }
                    catch
                    {
                        //throw;    
                    }
                }
            }
            return obj;
        }
        /// <summary>
        /// Gets the sp pkeys.
        /// </summary>
        /// <param name="tname">The tname.</param>
        /// <returns></returns>
        public List<SP_PKEYS> GetSP_PKEYS(string tname)
        {
            //查询标识列
            return Query<SP_PKEYS>($"EXEC SP_PKEYS @table_name = '{tname}'", null);
        }
        /// <summary>
        /// 确定指定的标识规范是否为标识规范
        /// </summary>
        /// <param name="IsIdentitys">The is identitys.</param>
        /// <param name="name">The name.</param>
        /// <returns>
        ///   <c>true</c> if the specified is identitys is identity; otherwise, <c>false</c>.
        /// </returns>
        private bool IsPK(List<SP_PKEYS> PKeyss, string name)
        {
            if (PKeyss != null)
            {
                foreach (var PKeys in PKeyss)
                {
                    if (PKeys.COLUMN_NAME.ToLower() == name.ToLower())
                    {
                        return true;
                    }
                }
            }
            return false;
        }
        /// <summary>
        /// Gets the information schemacolumns.
        /// </summary>
        /// <param name="tname">The tname.</param>
        /// <param name="COLUMN_NAME">Name of the column.</param>
        /// <returns></returns>
        public List<TableInfo> GetINFORMATION_SCHEMACOLUMNS(string tname, string COLUMN_NAME)
        {
            //查询标识列
            return Query<TableInfo>($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tname}' AND  COLUMNPROPERTY(OBJECT_ID('{tname}'), COLUMN_NAME, '{COLUMN_NAME}') = 1", null);
        }
        /// <summary>
        /// 确定指定的标识规范是否为标识规范
        /// </summary>
        /// <param name="IsIdentitys">The is identitys.</param>
        /// <param name="name">The name.</param>
        /// <returns>
        ///   <c>true</c> if the specified is identitys is identity; otherwise, <c>false</c>.
        /// </returns>
        private bool IsIdentity(List<TableInfo> IsIdentitys, string name)
        {
            if (IsIdentitys != null)
            {
                foreach (var Identity in IsIdentitys)
                {
                    if (Identity.COLUMN_NAME.ToLower() == name.ToLower())
                    {
                        return true;
                    }
                }
            }
            return false;
        }
        #endregion
        #region 获取sql语句
        public string GetAddSql<T>(T t)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            string sql = $"INSERT INTO {t.GetType().Name} ";
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();
            if (properties.Length <= 0)
                throw new Exception("该类成员数为 0 。");
            var tname = t.GetType().Name;
            //查询标识列
            var IsIdentitys = Query<TableInfo>($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = '{tname}' AND  COLUMNPROPERTY(OBJECT_ID('{tname}'), COLUMN_NAME, 'IsIdentity') = 1", null);
            string paramstr = " (";
            string paramValuestr = " VALUES (";
            bool fasheng = false;
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                string name = item.Name;
                if (IsIdentity(IsIdentitys, name))
                {
                    continue;
                }
                object value = item.GetValue(t, null);
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    if (fasheng)
                    {
                        paramstr += ",";
                        paramValuestr += ",";
                    }
                    fasheng = true;
                    paramstr += name;
                    paramValuestr += $"@{name}";
                    parms.Add(new SqlParameter() { ParameterName = name, Value = value });
                }
            }
            paramstr += ")";
            paramValuestr += ")";
            sql = sql + paramstr + paramValuestr;
            string parmstemp = "{";
            for (int i = 0; i < parms.Count; i++)
            {
                if (i > 0)
                {
                    parmstemp += ",";
                }
                SqlParameter item = parms[i];
                parmstemp += $"{item.ParameterName}:{item.Value}";
            }
            parmstemp += "}";
            return $"{parmstemp}" + sql;
        }
        public void GetAddSql<T>(T t, out string sql, out List<SqlParameter> parms)
        {
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();
            if (properties.Length <= 0)
                throw new Exception("该类成员数为 0 。");
            sql = $"INSERT INTO {t.GetType().Name} ";
            parms = new List<SqlParameter>();
            var tname = t.GetType().Name;
            //查询标识列
            var IsIdentitys = GetINFORMATION_SCHEMACOLUMNS(tname, "IsIdentity");
            string paramstr = " (";
            string paramValuestr = " VALUES (";
            bool fasheng = false;
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                string name = item.Name;
                if (IsIdentity(IsIdentitys, name))
                {
                    continue;
                }
                object value = item.GetValue(t, null);
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    if (value != null)
                    {
                        if (fasheng)
                        {
                            paramstr += ",";
                            paramValuestr += ",";
                        }
                        fasheng = true;
                        paramstr += name;
                        paramValuestr += $"@{name}";
                        parms.Add(new SqlParameter() { ParameterName = name, Value = value });
                    }
                }
            }
            paramstr += ")";
            paramValuestr += ")";
            sql = sql + paramstr + paramValuestr;
        }
        public void GetUpdateSql<T>(T t, out string sql, out List<SqlParameter> parms)
        {
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();
            if (properties.Length <= 0)
                throw new Exception("该类成员数为 0 。");
            sql = $"UPDATE {t.GetType().Name} ";
            parms = new List<SqlParameter>();
            var tname = t.GetType().Name;
            var pks = GetSP_PKEYS(tname);
            string paramSet = " SET ";
            string paramWhere = " WHERE ";
            bool fasheng1 = false;
            bool fasheng2 = false;
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                string name = item.Name;
                object value = item.GetValue(t, null);
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    if (!IsPK(pks, name))
                    {
                        if (fasheng1)
                            paramSet += ",";
                        paramSet += $"{name} = @{name}";
                        fasheng1 = true;
                    }
                    else
                    {
                        if (fasheng2)
                            paramWhere += ",";
                        paramWhere += $"{name} = @{name}";
                        fasheng2 = true;
                    }
                    parms.Add(new SqlParameter() { ParameterName = name, Value = value });
                }
            }
            sql = sql + paramSet + paramWhere;
        }

        public void GetDeleteSql<T>(T t, out string sql, out List<SqlParameter> parms)
        {
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties();
            if (properties.Length <= 0)
                throw new Exception("该类成员数为 0 。");
            sql = $"DELETE {t.GetType().Name} ";
            parms = new List<SqlParameter>();
            var tname = t.GetType().Name;
            var pks = GetSP_PKEYS(tname);
            string paramWhere = " WHERE ";
            bool fasheng = false;
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                string name = item.Name;
                object value = item.GetValue(t, null);
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    if (IsPK(pks, name))
                    {
                        if (fasheng)
                            paramWhere += ",";
                        paramWhere += $"{name} = @{name}";
                        fasheng = true;
                    }
                    parms.Add(new SqlParameter() { ParameterName = name, Value = value });
                }
            }
            sql = sql + paramWhere;
        }
        #endregion
        #region 操作数据

        /// <summary>
        /// 查询数据，返回List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">The SQL.</param>
        /// <param name="parms">The parms.</param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, SqlParameter[] parms = null)
        {
            var data = QueryDataTable(sql, parms, CommandType.Text);
            return data.Rows.Count > 0 ? ConvertTo<T>(data) as List<T> : default;
        }
        /// <summary>
        /// 新增数据，sql语句
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parms">The parms.</param>
        /// <returns></returns>
        public int Add(string sql, SqlParameter[] parms = null)
        {
            var result = Execute(sql, parms, CommandType.Text);
            return result;
        }
        /// <summary>
        /// 新增数据，对象数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t">The t.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">未将对象引用设置到对象的实例</exception>
        public int Add<T>(T t)
        {
            if (t == null)
                throw new Exception("未将对象引用设置到对象的实例");
            GetAddSql(t, out string sql, out List<SqlParameter> parms);
            return Execute(sql, parms.ToArray(), CommandType.Text);
        }
        /// <summary>
        /// 修改数据，根据主键
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t">The t.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">未将对象引用设置到对象的实例</exception>
        public int Update<T>(T t)
        {
            if (t == null)
                throw new Exception("未将对象引用设置到对象的实例");
            GetUpdateSql(t, out string sql, out List<SqlParameter> parms);
            return Execute(sql, parms.ToArray(), CommandType.Text);
        }
        /// <summary>
        /// 删除数据，根据主键
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t">The t.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">未将对象引用设置到对象的实例</exception>
        public int Delete<T>(T t)
        {
            if (t == null)
                throw new Exception("未将对象引用设置到对象的实例");
            GetDeleteSql(t, out string sql, out List<SqlParameter> parms);
            return Execute(sql, parms.ToArray(), CommandType.Text);
        }
        #endregion
    }
}
